insert overwrite table jms_dm.dm_agencyarea_prescription_reach_rate_dt partition(dt)
select taking_date as date_time  -- "日期",
  ,island -- "订单来源",
  ,districtprescription -- "区县时效",
  ,is_pres_sign -- "时效签收",
  ,is_real_sign -- "实际签收",
  ,start_subordinate_agent_code -- "始发代理区code",
  ,sign_subordinate_agent_code -- "签收代理区code",
  ,start_subordinate_agent_name -- "始发代理区name",
  ,start_regional_id -- "始发大区id",
  ,start_regional_desc -- "始发大区name",
  ,start_provider_id -- "始发省份id",
  ,start_provider_desc -- "始发省份name",
  ,sign_subordinate_agent_name -- "签收代理区name",
  ,sign_regional_id -- "签收大区id",
  ,sign_regional_desc -- "签收大区name",
  ,sign_provider_id -- "签收省份id",
  ,sign_provider_desc -- "签收省份name",
  ,economic_circle_type  -- 经济圈名称
  ,circle_type  -- 是否经济圈
  ,platform_effect_type  -- 是否平台时效
  ,platform_effect  -- 平台时效
  ,sum(case when is_real_standard=1 and is_real_sign=1 then 1 else 0 end) as real_reach_sum -- "实际达成票数",
  ,sum(case when (is_real_standard=0 or is_real_standard is null) and (is_real_sign=1 or is_real_sign=0) then 1 else 0 end) as real_no_reach_sum -- "实际未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) then 1 else 0 end) as real_total -- "实际总票量",
  ,sum(case when is_real_standard=1 and is_real_sign=1 then 1 else 0 end)/sum(case when (is_real_sign=1 or is_real_sign=0) then 1 else 0 end) as real_reach_rate -- "实际达成率",
  ,sum(case when is_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as pres_reach_sum -- "时效达成票数",
  ,sum(case when (is_pres_standard=0 or is_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) then 1 else 0 end) as pres_no_reach_sum -- "时效未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) then 1 else 0 end) as pres_total -- "时效总票量",
  ,sum(case when is_pres_standard=1 and is_pres_sign=1 then 1 else 0 end)/sum(case when (is_pres_sign=1 or is_pres_sign=0) then 1 else 0 end) as pres_reach_rate -- "时效达成率"

  ,sum(case when is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as real_platform_reach_sum -- "实际达成票数",
  ,sum(case when (is_platform_real_standard=0) and (is_real_sign=1 or is_real_sign=0)  and platform_effect_type=1 then 1 else 0 end) as real_platform_no_reach_sum -- "实际未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and platform_effect_type=1 then 1 else 0 end) as real_platform_total -- "实际总票量",
  ,sum(case when is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end)/sum(case when (is_real_sign=1 or is_real_sign=0) and platform_effect_type=1 then 1 else 0 end) as real_platform_reach_rate -- "实际达成率",
  ,sum(case when is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as pres_platform_reach_sum -- "时效达成票数",
  ,sum(case when is_platform_pres_standard=0 and (is_pres_sign=1 or is_pres_sign=0) and platform_effect_type=1 then 1 else 0 end) as pres_platform_no_reach_sum -- "时效未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and platform_effect_type=1 then 1 else 0 end) as pres_platform_total -- "时效总票量",
  ,sum(case when is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end)/sum(case when (is_pres_sign=1 or is_pres_sign=0) and platform_effect_type=1 then 1 else 0 end) as pres_platform_reach_rate -- "时效达成率"
  ,taking_date as dt
FROM jms_dm.dm_waybill_prescription_reach_details_dt
  where dt>='{{ execution_date | date_add(-6) | cst_ds }}'
   and dt<='{{ execution_date | cst_ds }}'
   and taking_date>='{{ execution_date | date_add(-6) | cst_ds }}'
   and taking_date<='{{ execution_date | cst_ds }}'
GROUP BY taking_date
  ,island
  ,districtprescription
  ,start_subordinate_agent_code
  ,start_subordinate_agent_name
  ,start_regional_id
  ,start_regional_desc
  ,start_provider_id
  ,start_provider_desc
  ,sign_subordinate_agent_code
  ,sign_subordinate_agent_name
  ,sign_regional_id
  ,sign_regional_desc
  ,sign_provider_id
  ,sign_provider_desc
  ,is_pres_sign
  ,is_real_sign
  ,economic_circle_type  -- 经济圈名称
  ,circle_type  -- 是否经济圈
  ,platform_effect_type  -- 是否平台时效
  ,platform_effect  -- 平台时效
     DISTRIBUTE BY 1 ;